Hide AllIn a page report, when you create a Multivalue list in a parameter, you can add an ALL value which removes the parameter filter to this list. Set the following to create an ALL parameter:
- A dataset to populate the parameter values
- A report parameter
- A list of values for the report parameter
- A dataset with a parameter
The following procedures take you through a step by step process of how to set an ALL parameter. These steps assume that you have added a page layout template to your report and have a data connection in place. See Adding an ActiveReport to a Project and Connect to a Data Source for further information.
 |
Note: This topic uses the Producers and Movie table from the Reels database. By default, in ActiveReports, the Reels.mdb file is located at [User Documents folder]\ComponentOne Samples\ActiveReports Developer 7\Data\Reels.mdb. |
To create a dataset to populate the parameter values
- In the Report Explorer, right-click the Data Source (DataSource1 by default) node and select Add Data Set.
- In the DataSet dialog that appears, select the Query page.
- Enter a SQL query like the following into the Query text box where a UNION SELECT statement combines the results of the ALL query with those of the query for the individual values.
SELECT -1 AS ProductionID, "(All)" AS Name
FROM Producers
UNION
SELECT ProductionID, Name
FROM Producers; - Click the OK button on the lower right corner to close the dialog and see the dataset and the selected fields appear in the Report Explorer.
- In the Report Explorer, right-click the Parameters node and select Add Parameter.
- In the Report - Parameters dialog that appears, add a name for the parameter (i.e. ProdID) and ensure that the Data type matches that of the field (i.e. Integer for ProductionID).
- Enter Text for prompting users for a value.
- Select the check box next to Multivalue to allow users to select more than one item from the list.
To provide a list of values for the Report Parameter
- In the Report - Parameters dialog, go to the Available Values tab and select the From query radio button.
- Under the Dataset field, select the dataset created above (i.e. DataSet1).
- Under the Value field, select the field given for -1 (i.e. ProductionID).
- Under Label field, select the field given for "All" (i.e. Name).
- Click the OK button on the lower right corner to close the dialog and add the parameter to the collection.
To add a dataset with a parameter
- In the Report Explorer, right-click the Data Source (DataSource1 by default) node and select Add Data Set.
- In the DataSet dialog that appears, on the Parameters page, click the Add (+) icon above the parameters list and add the following to the dataset to provide values for the parameters we add to the query in step 3 below.
Name: Param1; Value: =Parameters!ProdID.Value
Name: Param2; Value: =Parameters!ProdID.Value - On the Query page, enter a SQL query like the following in the Query text box:
SELECT Movie.Title, Movie.YearReleased, Movie.UserRating, Producers.Name
FROM Producers INNER JOIN (Movie INNER JOIN MovieProducers ON Movie.MovieID = MovieProducers.MovieID) ON Producers.ProductionID = MovieProducers.ProductionID
WHERE (MovieProducers.ProductionID IN (?)) OR (-1 IN (?))
ORDER BY MovieProducers.ProductionID, Movie.YearReleased - Click the Validate DataSet icon to validate the query and to populate the Fields list.
- Click the OK button on the lower right corner to close the dialog and see the dataset and the selected fields appear in the Report Explorer.
Place a control like a Table onto the design surface and add fields to it. View the report in the preview tab and see the Parameters in the sidebar with an ALL option at the top.
|
Note: In a fixed page layout (FPL), when you have multiple datasets in the report, you need to set the DataSet property on the General tab of the FixedPage dialog in order to specify which dataset is used to display data in the report. |
See Also
Concepts
Parameters